﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using DBHelper;

namespace DemoApplication
{
    public class SQLServerDemo
    {
        private string connectionString = "";
        private DbUtility dbUtility = null;
        private const string SQL_QueryUser = "SELECT [UserID],[UserName],[RealName],[Age],[Sex],[Mobile],[Phone],[Email] FROM [UserInfo] WHERE [UserID]=@UserID";
        private const string SQL_AddUser = "INSERT INTO [UserInfo]([UserName],[RealName],[Age],[Sex],[Mobile],[Phone],[Email])VALUES(@UserName,@RealName,@Age,@Sex,@Mobile,@Phone,@Email)";
        private const string SQL_UpdateUser = "UPDATE [UserInfo] SET [UserName] = @UserName,[RealName] = @RealName,[Age] = @Age,[Sex] = @Sex,[Mobile] = @Mobile,[Phone] = @Phone ,[Email] = @Email WHERE [UserID]=@UserID";
        private const string SQL_DeleteUser = "DELETE FROM [UserInfo] WHERE [UserID]=@UserID";

        public SQLServerDemo()
        {
            DbProviderFactory dbProviderFactory = SqlClientFactory.Instance;
            //dbProviderFactory = System.Data.OleDb.OleDbFactory.Instance;
            //dbProviderFactory=System.Data.SQLite.SQLiteDbFactory.Instance;
            dbUtility = new DbUtility(connectionString, dbProviderFactory);
        }

        public DataTable GetUsers(int userId)
        {
            IList<DbParameter> parameters = new List<DbParameter>(5);
            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@UserID",
                    Value =userId
                });
            DataTable data = dbUtility.ExecuteDataTable(SQL_QueryUser, parameters);
            Console.WriteLine("GetUsers():" + data.Rows.Count);
            return data;
        }

        public List<UserInfo> GetUserList(DataTable dataTable)
        {
            List<UserInfo> userList=EntityReader.GetEntities<UserInfo>(dataTable);
            Console.WriteLine("GetUserList():" + userList.Count);
            return userList;
        }

        public bool AddUsers(UserInfo userInfo)
        {
            IList<DbParameter> parameters = new List<DbParameter>(8);

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Age",
                    Value = userInfo.Age
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Email",
                    Value = userInfo.Email
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Mobile",
                    Value = userInfo.Mobile
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Phone",
                    Value = userInfo.Phone
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@RealName",
                    Value = userInfo.RealName
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Sex",
                    Value = userInfo.Sex
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@UserName",
                    Value = userInfo.UserName
                });

            int count= dbUtility.ExecuteNonQuery(SQL_AddUser, parameters);

            return count > 0;
        }


        public bool DeleteUser(int userId)
        {
            IList<DbParameter> parameters = new List<DbParameter>(8);
            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@UserID",
                    Value = userId
                });

            return dbUtility.ExecuteNonQuery(SQL_DeleteUser, parameters) > 0;
        }

        public bool UpdateUser(UserInfo userInfo)
        {
            IList<DbParameter> parameters = new List<DbParameter>(8);
            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@UserID",
                    Value = userInfo.UserId
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Age",
                    Value = userInfo.Age
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Email",
                    Value = userInfo.Email
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Mobile",
                    Value = userInfo.Mobile
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Phone",
                    Value = userInfo.Phone
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@RealName",
                    Value = userInfo.RealName
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@Sex",
                    Value = userInfo.Sex
                });

            parameters.Add(
                new SqlParameter
                {
                    ParameterName = "@UserName",
                    Value = userInfo.UserName
                });

            int count = dbUtility.ExecuteNonQuery(SQL_UpdateUser, parameters);

            return count > 0;
        }

    }
}
